package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.Page;
import com.lanqiao.guimei.vo.GoodItem;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 商品列表对应的实体类GoodItem
 * @author StarsOne
 * @date Create in  2019-8-17 0017 10:19:21
 * @description
 */
public class GoodItemDao extends BaseDao {


    /**
     * 查询大分类的总记录数
     * @param type
     * @return
     */
    public int queryAllRowByType(String type) {
        try {
            return Integer.valueOf(runner.query("select count(1) from PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO = s.PROD_NO and p.VEN_ID = v.VEN_ID and p.CTGR_ID in (select CTGR_ID from CATEGORY where BIG_CTGR = ?)", new ScalarHandler<>(), type).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询某个大分类下面的数据
     * @param type 大分类
     * @return
     */
    public List<GoodItem> queryByType(String type) {
        try {
            return runner.query("select distinct p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO = s.PROD_NO and p.VEN_ID = v.VEN_ID and p.CTGR_ID in (select CTGR_ID from CATEGORY where BIG_CTGR = ?)", new BeanListHandler<>(GoodItem.class), type);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据大分类分页查询
     * @param type 大分类
     * @return
     */
    public List<GoodItem> queryByType(Page<GoodItem> page, String type) {
        try {
            return runner.query("select * from (select ps.*,ROWNUM rn from (select distinct p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO = s.PROD_NO and p.VEN_ID = v.VEN_ID and p.CTGR_ID in (select CTGR_ID from CATEGORY where BIG_CTGR = ?) ) ps where ROWNUM<=?) where rn>?", new BeanListHandler<>(GoodItem.class), type, page.getLastIndex(), page.getFirstIndex() - 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    /**
     * 根据小分类id查询商品
     * @param ctgrId
     * @return
     */
    public List<GoodItem> queryByCtgrId(int ctgrId) {
        try {
            return runner.query("select p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from  PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO=s.PROD_NO and p.VEN_ID=v.VEN_ID and p.CTGR_ID=?", new BeanListHandler<>(GoodItem.class), ctgrId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<GoodItem> queryNew() {
        try {
            return runner.query("select p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from  PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO=s.PROD_NO and p.VEN_ID=v.VEN_ID and p.PROD_NO=s.PROD_NO and current_date-s.STAR_TIME>0 order by current_date-s.STAR_TIME", new BeanListHandler<>(GoodItem.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 查询抢购商品 (打折的价格较多的排前面）
     * @return
     */
    public List<GoodItem> queryHot() {
        //p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName
        try {
            return runner.query("select p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from  PRODUCT p,SALE_PROD s,VENDOR v where p.PROD_NO=s.PROD_NO and p.VEN_ID=v.VEN_ID order by p.PRICE-s.REAL_PRICE desc ", new BeanListHandler<>(GoodItem.class));
        } catch (SQLException e) {
            System.err.println("查询抢购商品错误："+e.getMessage());
        }
        return null;
    }

    public List<GoodItem> queryByName(String name) {

        try {
            return runner.query("select p.PROD_NO prodNo,p.PRICE price,s.REAL_PRICE realPrice,p.IMAGE image,p.DESCR descr,VEN_NAME venName from  PRODUCT p,SALE_PROD s,VENDOR v,CATEGORY c where p.PROD_NO=s.PROD_NO and p.VEN_ID=v.VEN_ID and p.CTGR_ID =c.CTGR_ID and concat(concat(p.PROD_NAME,c.CTGR_NAME),p.DESCR) like ?", new BeanListHandler<>(GoodItem.class), "%" + name + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    

}
